package maui.vocab;

import java.io.File;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;
import java.util.zip.GZIPInputStream;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;

import maui.stemmers.PorterStemmer;
import maui.stemmers.Stemmer;
import maui.stopwords.Stopwords;
import maui.stopwords.StopwordsEnglish;

import com.hp.hpl.jena.rdf.model.Model;
import com.hp.hpl.jena.rdf.model.ModelFactory;
import com.hp.hpl.jena.rdf.model.Property;
import com.hp.hpl.jena.rdf.model.RDFNode;
import com.hp.hpl.jena.rdf.model.Resource;
import com.hp.hpl.jena.rdf.model.Statement;
import com.hp.hpl.jena.rdf.model.StmtIterator;

/**
 * Indexes the content of the controlled vocabulary into an embedded H2
 * database. Accepts vocabularies only as rdf files (SKOS format).

 * @author craig.willis
 */

public class MauiVocabularyH2 implements MauiVocabulary
{
	/* Location of SKOS RDF/XML file */
	private static File SKOS;

    /* FileWriters used during H2 database initialization */
    FileWriter vocConcept;
    FileWriter vocAlternate;
    FileWriter vocRelated;
	
	/** Document language */
	private String language = "en";

	/** Document encoding */
	private String encoding = "UTF-8";

	/** Default stemmer to be used */
	private Stemmer stemmer;

	/** List of stopwords to be used */
	private Stopwords stopwords;

	/** Normalization to lower case - defaulte no */
	private boolean toLowerCase = true;

	/** Normalization via alphabetic reordering - default true*/
	private boolean reorder = true;
	
	private boolean debugMode = false;

	private String vocabularyName = "";
	private File h2file = null;

	public static void main(String[] args) throws Exception
	{
	    String name = "agrovoc";
	    String dir = "data/vocabularies";
	    String h2Path = "data/agrovoc/agrovoc";
	    
	    MauiVocabularyJena v = new MauiVocabularyJena(name, "skos", dir);
	    v.setEncoding("UTF-8");
        v.setLanguage("en");
        v.setLowerCase(true);
        v.setReorder(true);
        v.setStemmer(new PorterStemmer());;
        v.setStopwords(new StopwordsEnglish("data/stopwords/stopwords_en.txt"));
        v.initialize();
       
        List<String> senses = v.getSenses("Flavouring");
        for (String sense: senses)
            System.out.println(sense);
        System.out.println("--");
        String id = v.getID("amaranthus");
        System.out.println(id);
        System.out.println("--");
        
	    MauiVocabularyH2 h2 = new MauiVocabularyH2(name, dir, h2Path);
	    h2.setEncoding("UTF-8");
	    h2.setLanguage("en");
	    h2.setLowerCase(true);
	    h2.setReorder(true);
	    h2.setStemmer(new PorterStemmer());;
	    h2.setStopwords(new StopwordsEnglish("data/stopwords/stopwords_en.txt"));
	    //h2.buildSKOS();
	    senses = h2.getSenses("Flavouring");
	    for (String sense: senses)
	        System.out.println(sense);
	    System.out.println("--");
	    id = h2.getID("amaranthus");
	    System.out.println(id);
	    /*
	    cytochrom   4
	    http://www.fao.org/aos/agrovoc#c_32421 cytochrom   Cytochrome c
        http://www.fao.org/aos/agrovoc#c_32422  cytochrom   Cytochrome f
        http://www.fao.org/aos/agrovoc#c_27497  cytochrom   Cytochromes
        http://www.fao.org/aos/agrovoc#c_32420  cytochrom   Cytochrome b

	    vitamin 4
	    http://www.fao.org/aos/agrovoc#c_8271  vitamin Vitamin d
        http://www.fao.org/aos/agrovoc#c_8274   vitamin Vitamins
        http://www.fao.org/aos/agrovoc#c_8275   vitamin Vitamins B
        http://www.fao.org/aos/agrovoc#c_15230  vitamin Vitamin e
	    
	    steril  4
	    http://www.fao.org/aos/agrovoc#c_7401  steril  Sterilization
        http://www.fao.org/aos/agrovoc#c_7402   steril  Sterilizing
        http://www.fao.org/aos/agrovoc#c_36499  steril  Sterilizers
        http://www.fao.org/aos/agrovoc#c_24899  steril  Self sterility

	    flavour 4
	    http://www.fao.org/aos/agrovoc#c_2967  flavour Flavouring
        http://www.fao.org/aos/agrovoc#c_2969   flavour Flavourings
        http://www.fao.org/aos/agrovoc#c_10893  flavour Flavour
        http://www.fao.org/aos/agrovoc#c_28336  flavour Off flavours
	    */

	}
	/** Vocabulary constructor. 
	 * 
	 * Given the name of the vocabulary and the format, it first checks whether
	 * the data/vocabularies directory contains the specified files:<br>
	 * - vocabularyName.rdf if skos format is selected<br>
	 * - or a set of 3 flat txt files starting with vocabularyName and with extensions<br>
	 * <li>.en (id term)
	 * <li>.use (non-descriptor \t descriptor)
	 * <li>.rel (id \t related_id1 related_id2 ...)
	 * If the required files exist, the vocabulary index is built.
	 * 
	 * @param vocabularyName The name of the vocabulary file (before extension).
	 * @param vocabularyFormat The format of the vocabulary (skos or text).
	 * @throws Exception 
	 * */
	public MauiVocabularyH2(String vocabularyName, String vocabularyDirectory, String h2Path)
			throws Exception {

	    this.vocabularyName = vocabularyName;
	    
		SKOS = new File(vocabularyDirectory + "/" + vocabularyName + ".rdf.gz");
		
		if (!SKOS.exists())
			throw new Exception("File " + SKOS.getAbsolutePath() + " not found!");
		
		h2file = new File(h2Path + ".h2.db");
        // Initialize an H2 connection pool
        String uri = "jdbc:h2:" + h2Path;
        Class.forName("org.h2.Driver");

        ObjectPool connectionPool = new GenericObjectPool(null);
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(uri, "", "");
        new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);

        Class.forName("org.apache.commons.dbcp.PoolingDriver");
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:"); 
        driver.registerPool(vocabularyName, connectionPool);
	}

	public void setLanguage(String language) {
		this.language = language;
	}

	public void setEncoding(String encoding) {
		this.encoding = encoding;
	}

	public void setLowerCase(boolean toLowerCase) {
		this.toLowerCase = toLowerCase;
	}

	public void setReorder(boolean reorder) {
		this.reorder = reorder;
	}

	public void setStemmer(Stemmer stemmer) {
		this.stemmer = stemmer;
	}
	
	public void setDebug(boolean debugMode) {
		this.debugMode = debugMode;
	}

	/**
	 * Starts initialization of the vocabulary.
	 * @throws Exception 
	 *
	 */
	public void initialize() throws Exception {
	    if (!h2file.exists())
	        buildSKOS();
	}

	/**
	 * Set the stopwords class.
	 * @param stopwords 
	 */
	public void setStopwords(Stopwords stopwords) {
		this.stopwords = stopwords;
	}

	/**
	 * Builds the vocabulary indexes from SKOS file.
	 */
	public void buildSKOS() throws Exception {
		
		if (debugMode) {
			System.err.println("--- Building the Vocabulary index from the SKOS file...");
		}	
		
        // Temporary files used to store Maui maps prior to import into H2
        File fileConcept = File.createTempFile("concept", null);
        File fileAlternate = File.createTempFile("alternate", null);
        File fileRelated = File.createTempFile("related", null);
        
        System.out.println("Creating temp file " + fileConcept.getAbsolutePath());
        System.out.println("Creating temp file " + fileAlternate.getAbsolutePath());
        System.out.println("Creating temp file " + fileRelated.getAbsolutePath());
        
        vocConcept = new FileWriter(fileConcept);
        vocAlternate = new FileWriter(fileAlternate);
        vocRelated = new FileWriter(fileRelated);

		Model model = ModelFactory.createDefaultModel();
		
		
		InputStream gzipStream = new GZIPInputStream(new FileInputStream(SKOS));
		model.read(new InputStreamReader(gzipStream,encoding),"");
		
		// FileInputStream fileStream = new FileInputStream(SKOS);
		//model.read(new InputStreamReader(fileStream,encoding),"");

		StmtIterator iter;
		Statement stmt;
		Resource concept;
		Property property;
		RDFNode value;

		// to create IDs for non-descriptors!
		int count = 0;
		// Iterating over all statements in the SKOS file
		iter = model.listStatements();

		int concepts = 0;
		int altLabels = 0;
		int relatedConcepts = 0;
		while (iter.hasNext()) {
			stmt = iter.nextStatement();

			// id of the concept (Resource), e.g. "c_4828"
			concept = stmt.getSubject();
			String id = concept.getURI();

			// relation or Property of the concept, e.g. "narrower"
			property = stmt.getPredicate();
			String relation = property.getLocalName();

			// value of the property, e.g. c_4828 has narrower term "c_4829"
			value = stmt.getObject();
			String name = value.toString();

			if (relation.equals("prefLabel")) {
			    concepts++;
				String descriptor, language;
				int atPosition = name.indexOf('@');
				if (atPosition != -1) {
					language = name.substring(atPosition + 1);
					name = name.substring(0, atPosition);
					if (language.equals(this.language))
						descriptor = name;
					else
						continue;

				} else {
					descriptor = name;
				}

				String descriptorNormalized = normalizePhrase(descriptor);

				if (descriptorNormalized.length() >= 1) {
				    addConcept(id, descriptorNormalized, descriptor);
				}

			} else if (relation.equals("altLabel")
					|| (relation.equals("hiddenLabel"))) {
			    altLabels++;
				String non_descriptor, language;

				int atPosition = name.indexOf('@');
				if (atPosition != -1) {
					language = name.substring(atPosition + 1);
					name = name.substring(0, atPosition);
					if (language.equals(this.language))
						non_descriptor = name;
					else
						continue;

				} else {
					non_descriptor = name;
				}

				addNonDescriptor(count, id, non_descriptor);
				count++;
            } else if (relation.equals("broader")) {
                relatedConcepts++;
                String relatedId = name;
                addBroader(id, relatedId);
            } else if (relation.equals("narrower")) {
                relatedConcepts++;
                String relatedId = name;
                addNarrower(id, relatedId);
            } else if (relation.equals("related")) {
                relatedConcepts++;
                String relatedId = name;
                addRelated(id, relatedId);
			}
		}
	
        // Close the writers
        vocConcept.close();
        vocAlternate.close();
        vocRelated.close();
        
        java.sql.Connection con = null;
        java.sql.Statement s = null;
        try {

            con = getConnection();
            s = con.createStatement();
        
            s.execute("CREATE TABLE concept (uri varchar(512), normalized varchar(1024), orig varchar(1024)) AS SELECT * FROM CSVREAD('" + fileConcept.getAbsolutePath() + "',null, 'UTF-8', '|');");
            s.execute("CREATE INDEX idx_concept1 on concept(uri);");
            s.execute("CREATE INDEX idx_concept2 on concept(normalized);");
            s.execute("CREATE INDEX idx_concept3 on concept(orig);");
            
            if (relatedConcepts > 0)
                s.execute("CREATE TABLE related (uri1 varchar(512), uri2 varchar(1024), relation varchar(20)) AS SELECT * FROM CSVREAD('" + fileRelated.getAbsolutePath() + "',null, 'UTF-8', '|');");
            else
                s.execute("CREATE TABLE related (uri1 varchar(512), uri2 varchar(1024), relation varchar(20));");

            s.execute("CREATE INDEX idx_related1 on related(uri1);");
            s.execute("CREATE INDEX idx_related2 on related(uri2);");
                    
            if (altLabels > 0) 
                s.execute("CREATE TABLE alternate ( uri varchar(512) , normalized varchar(1024), orig varchar(1024)) AS SELECT * FROM CSVREAD('" + fileAlternate.getAbsolutePath() + "',null, 'UTF-8', '|');");
            else
                s.execute("CREATE TABLE alternate ( uri varchar(512) , normalized varchar(1024), orig varchar(1024));");
            
            s.execute("CREATE INDEX idx_alternate1 on alternate(uri);");     
            s.execute("CREATE INDEX idx_alternate2 on alternate(normalized);");
            s.execute("CREATE INDEX idx_alternate3 on alternate(orig);");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                try {
                    if (con != null)
                        con.close();
                    if (s != null)
                        s.close();
                } catch (Exception e) { }
            }
        }
        
        // Delete the temporary files
        fileConcept.delete();
        fileAlternate.delete();
        fileRelated.delete();
		if (debugMode) {
			System.err.println("--- Statistics about the vocabulary: ");
			System.err.println("\t" + concepts + " terms in total");
			System.err.println("\t" + altLabels+ " non-descriptive terms");
			System.err.println("\t" + relatedConcepts + " terms have related terms");
		}
	}


    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:apache:commons:dbcp:" + vocabularyName);
    }    
    
	/**
	 * Returns the id of the given term
	 * @param phrase
	 * @return term id
	 */
    public String getID(String phrase) 
    {
        String id = null;
        if (phrase != null) {
            Connection con = null;
            PreparedStatement ps = null;
            PreparedStatement ps2 = null;
            try {
                String sql = "select uri from concept where orig = ?";
                
                con = getConnection();
                ps = con.prepareStatement(sql);
                ps.setString(1, phrase.toLowerCase());
                ResultSet rs = ps.executeQuery();
                while (rs.next())
                    id = rs.getString(1);
                
                if (id == null)
                {
                    String sql2 = "select uri from alternate where orig = ?";
    
                    ps2 = con.prepareStatement(sql2);
                    ps2.setString(1, phrase);
                    ResultSet rs2 = ps2.executeQuery();
                    while (rs2.next())
                    {
                        id = rs2.getString(1);
                    }
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try
                {
                    if (con != null) 
                        con.close();
                    if (ps != null)
                        ps.close();
                    if (ps2 != null)
                        ps2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return id;
    }

    /**
     * Returns the id of the given term
     * @param phrase
     * @return term id
     */
    public Vector<String> getIDs(String normalized) 
    {
        Vector<String> ids = new Vector<String>();

        String id = null;
        if (normalized != null) {
            Connection con = null;
            PreparedStatement ps = null;
            PreparedStatement ps2 = null;
            try {
                String sql = "select uri from concept where normalized = ?";
                
                con = getConnection();
                ps = con.prepareStatement(sql);
                ps.setString(1, normalized);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    id = rs.getString(1);
                    ids.add(id);
                }
                
                String sql2 = "select uri from alternate where normalized = ?";

                ps2 = con.prepareStatement(sql2);
                ps2.setString(1, normalized);
                ResultSet rs2 = ps2.executeQuery();
                while (rs2.next())
                {
                    id = rs2.getString(1);
                    ids.add(id);
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try
                {
                    if (con != null) 
                        con.close();
                    if (ps != null)
                        ps.close();
                    if (ps2 != null)
                        ps2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return ids;
    }


	/**
	 * Checks whether a normalized phrase 
	 * is a valid vocabulary term.
	 * @param phrase
	 * @return true if phrase is in the vocabulary
	 */
	public boolean containsNormalizedEntry(String phrase) {
		return getIDs(normalizePhrase(phrase)).size() > 0;
	}

	/**
	 * Returns true if a phrase has more than one senses
	 * @param phrase
	 * @return false if a phrase has only one sense
	 */
	public boolean isAmbiguous(String phrase) {
	    return getIDs(normalizePhrase(phrase)).size() > 1;
	}

	/**
	 * Retrieves all possible descriptors for a given phrase
	 * @param phrase
	 * @return a vector list of all senses of a given term
	 */
	public Vector<String> getSenses(String phrase) 
	{
		String normalized = normalizePhrase(phrase);

		return getIDs(normalized);
	}
	
 
	
	/** 
	 * Generates the preudo phrase from a string.
	 * A pseudo phrase is a version of a phrase
	 * that only contains non-stopwords,
	 * which are stemmed and sorted into alphabetical order. 
	 */
	public String normalizePhrase(String phrase) {		
		
		if (toLowerCase) {
			phrase = phrase.toLowerCase();
		}
		
		if (toLowerCase) {
			phrase = phrase.toLowerCase();
		}
		StringBuffer result = new StringBuffer();
		char prev = ' ';
		int i = 0;
		while (i < phrase.length()) {
			char c = phrase.charAt(i);
			
			// we ignore everything after the "/" symbol and everything in brackets
			// e.g. Monocytes/*immunology/microbiology -> monocytes
			// e.g. Vanilla (Spice) -> vanilla
			if (c == '/' || c == '(') 
				break;
			
			if (c == '-' ||  c == '&' || c == '.' || c == '.') 
				c = ' ';
				
			if (c == '*' || c == ':') {
				prev = c;
				i++;
				continue;
			}
			
			if (c != ' ' || prev != ' ')
				result.append(c);
			
			prev = c;
			i++;
		}

	
		phrase = result.toString().trim();
		
		if (reorder || stopwords != null || stemmer != null) {
			phrase = pseudoPhrase(phrase);
		} 
		if (phrase.equals("")) {
			// to prevent cases where the term is a stop word (e.g. Back).
			return result.toString(); 
		} else {
			return phrase;
		}
	}

	/** 
	 * Generates the preudo phrase from a string.
	 * A pseudo phrase is a version of a phrase
	 * that only contains non-stopwords,
	 * which are stemmed and sorted into alphabetical order. 
	 */
	public String pseudoPhrase(String str) {
		String result = "";
		String[] words = str.split(" ");
		if (reorder) {
			Arrays.sort(words);
		}
		for (String word : words) {

			if (stopwords != null) {
				if (stopwords.isStopword(word)) {
					continue;
				}
			}

			int apostr = word.indexOf('\'');
			if (apostr != -1) {
				word = word.substring(0, apostr);
			}

			if (stemmer != null) {
				word = stemmer.stem(word);
			}
			result += word + " ";
		}
		return result.trim();
	}

    private void addConcept(String uri, String normalized, String preferredLabel) 
    {
        try {
            vocConcept.write(uri + "|" + normalized + "|" + preferredLabel.toLowerCase() + "\n");         
        } catch (IOException e) {
            e.printStackTrace();
        }       
    }
    

    private void addBroader(String uri, String uriBroader) 
    {
        try {
            vocRelated.write(uri + "|" + uriBroader + "|broader\n");
        } catch (IOException e) {
            e.printStackTrace();
        }       
    }
    
    
    private void addNarrower (String uri, String uriNarrower) 
    {
        try {
            vocRelated.write(uri + "|" + uriNarrower + "|narrower\n");
        } catch (IOException e) {
            e.printStackTrace();
        }       
    }
        
    private void addRelated (String uri, String uriRelated) 
    {
        try {
            vocRelated.write(uri + "|" + uriRelated + "|related\n");
            vocRelated.write(uriRelated + "|" + uri + "|related\n");
        } catch (IOException e) {
            e.printStackTrace();
        }       
    }
    
    private void addNonDescriptor(int count, String uri, String altLabel) 
    {

        String normalized = this.normalizePhrase(altLabel);
        
        try {
            vocAlternate.write(uri + "|" + normalized + "|" + altLabel.toLowerCase() + "\n"); 
        } catch (IOException e) {
            e.printStackTrace();
        }   
    }
    
    /**
     * Returns the term for the given id
     * @param id - id of some phrase in the vocabulary
     * @return phrase, i.e. the full form listed in the vocabulary
     */
    public String getTerm(String id) 
    {
        String orig = null;

        Connection con = null;
        PreparedStatement ps = null;
        try {
            String sql = "select orig from concept where uri = ?";
            
            con = getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next())
                orig = rs.getString(1);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try
            {
                if (con != null) 
                    con.close();
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
            
        return orig;
    }


    public Vector<String> getRelated(String id) 
    {
        
        Vector<String> related = new Vector<String>();

        Connection con = null;
        PreparedStatement ps = null;
        try {
            String sql = "select orig from related where uri = ? and relation = 'related'";
            
            con = getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                related.add(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try
            {
                if (con != null) 
                    con.close();
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return related;
        
    }

    /**
     * Given an ID of a term gets the list of all IDs of terms
     * that are semantically related to the given term
     * with a specific relation
     * @param id - id of some term in the vocabulary
     * @param relation - a given semantic relation
     * @return a vector with ids related to the input id by a specified relation
     */
    public Vector<String> getRelated(String id, String relation) 
    {
        Vector<String> related = new Vector<String>();

        Connection con = null;
        PreparedStatement ps = null;
        try {
            String sql = "select orig from related where uri = ? and relation = ?";
            
            con = getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            ps.setString(2, relation);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                related.add(rs.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try
            {
                if (con != null) 
                    con.close();
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return related;
    }
}
